import pandas as pd
from openai import OpenAI
from typing import Tuple
import time
from tqdm import tqdm

def evaluate_translation(client: OpenAI, row) -> Tuple[int, int, str]:
    """评估两种翻译质量并返回人工翻译分数、谷歌翻译分数和理由"""
    
    prompt = f"""评估以下两种翻译质量:
原文: {row['原文'][:1000]}
人工翻译: {row['译文'][:1000]}
谷歌翻译: {row['谷歌译文'][:1000]}

请分别为人工翻译和谷歌翻译评分，从准确性、流畅性、语法三方面评估，并说明哪个翻译更好。按以下格式回复：
人工翻译分数: [0-100]
谷歌翻译分数: [0-100]
评价: [说明哪个翻译更好及原因]"""

    max_retries = 5
    base_delay = 20
    
    for attempt in range(max_retries):
        try:
            response = client.chat.completions.create(
                model="gpt-4o-mini",
                messages=[{"role": "user", "content": prompt}],
                temperature=0.7,
                max_tokens=200
            )
            
            result = response.choices[0].message.content
            
            try:
                # 默认值
                human_score = google_score = 50
                reason = "未能正确解析响应"
                
                # 提取人工翻译分数
                if '人工翻译分数' in result:
                    score_parts = result.split('人工翻译分数')[1].split('\n')[0].strip(':： ')
                    numbers = ''.join(filter(str.isdigit, score_parts))
                    if numbers:
                        human_score = max(0, min(100, int(numbers)))
                
                # 提取谷歌翻译分数
                if '谷歌翻译分数' in result:
                    score_parts = result.split('谷歌翻译分数')[1].split('\n')[0].strip(':： ')
                    numbers = ''.join(filter(str.isdigit, score_parts))
                    if numbers:
                        google_score = max(0, min(100, int(numbers)))
                
                # 提取评价
                reason_markers = ['评价', '理由', '分析']
                for marker in reason_markers:
                    if marker in result:
                        parts = result.split(marker)
                        if len(parts) > 1:
                            reason = parts[1].strip(':： ')
                            break
                
                return human_score, google_score, reason
                
            except (IndexError, ValueError) as e:
                print(f"\n解析响应失败: {str(e)}")
                print(f"原始响应: {result}")
                return 50, 50, f"解析失败，原始响应: {result}"
            
        except Exception as e:
            wait_time = base_delay * (2 ** attempt)
            print(f"\n评估失败 ({attempt + 1}/{max_retries}): {str(e)}")
            print(f"等待 {wait_time} 秒后重试...")
            time.sleep(wait_time)
            
            if attempt == max_retries - 1:
                return 50, 50, f"评估失败: {str(e)}"

    return 50, 50, "多次重试后仍然失败"

def process_excel(input_file: str, output_file: str, api_key: str, start_index: int = 0):
    """处理Excel文件并添加评价列"""
    
    # 初始化OpenAI客户端，设置base_url
    client = OpenAI(
        api_key=api_key,
        base_url="https://api.chatanywhere.tech"  # 设置ChatAnywhere的API端点
        # base_url="https://api.tu-zi.com/v1"
    )
    
    # 读取Excel文件
    print(f"正在读取文件: {input_file}")
    df = pd.read_excel(input_file)
    
    # 如果是新开始的评估，添加新列
    if start_index == 0:
        df['人工译文评分'] = ''
        df['谷歌译文评分'] = ''
        df['评价理由'] = ''
    
    # 使用tqdm显示进度条，从指定位置开始
    for index, row in tqdm(df.iloc[start_index:].iterrows(), 
                          total=len(df)-start_index, 
                          desc="评估进度"):
        try:
            human_score, google_score, reason = evaluate_translation(client, row)
            df.at[index, '人工译文评分'] = human_score
            df.at[index, '谷歌译文评分'] = google_score
            df.at[index, '评价理由'] = reason
            
            # 每5条保存一次
            if index % 5 == 0:
                df.to_excel(output_file, index=False)
                print(f"\n已保存至第 {index} 行")
            
            # 添加较长的延迟以避免频率限制
            time.sleep(3)
            
        except Exception as e:
            print(f"\n处理第 {index} 行时出错: {e}")
            print(f"已保存当前进度，可以从第 {index} 行继续处理")
            df.to_excel(output_file, index=False)
            raise
    
    # 最终保存
    df.to_excel(output_file, index=False)
    print(f"\n处理完成，结果已保存至 {output_file}")

if __name__ == "__main__":
    #API key
    API_KEY = "sk-8r4yikeXCMIrSdq01K2bKCnKjrbAk97NY2bJPAhfnyjXXeFK"
    # API_KEY = "sk-PEvjpQELoLDQqNX75f8c457d2cC244E4846a4c13C497Fd73"

    # 从上次保存的位置继续
    START_INDEX = 0
    
    try:
        # 处理文件
        process_excel(
            "translation_dataset_正式翻译.xlsx",
            "正式翻译_llm.xlsx",
            API_KEY,
            START_INDEX
        )
    except Exception as e:
        print(f"\n程序异常终止: {e}")
        print("可以修改 START_INDEX 从中断处继续处理")